package com.exm.collectcodenew.web.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.exm.collectcodenew.common.vo.ResultMap;
import com.exm.collectcodenew.util.jdbc.JDBCConnFactory;
import com.exm.collectcodenew.web.entity.Works;
import com.exm.collectcodenew.web.service.WorkCommentDetailBySqlService;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class WorkCommentDetailBySqlServiceImpl implements WorkCommentDetailBySqlService {
    @Override
    public ResultMap selectDetails(Works works)  {
        if(ObjectUtils.isEmpty(works.getCurrent())||ObjectUtils.isEmpty(works.getSize())){
            return ResultMap.fail("请设置分页参数");
        }
        LocalDate startDate = LocalDate.parse(works.getStartDate());
        LocalDate endDate = LocalDate.parse(works.getEndDate());
        if(ObjectUtils.isEmpty(startDate)||ObjectUtils.isEmpty(endDate)){
            return ResultMap.fail("请填写开始时间和结束时间");
        }
        if(startDate.isAfter(endDate)){
            return ResultMap.fail("开始时间不能晚于结束时间");
        }
        //根据日期区间和特定条件生成要查询的sql（查询日期区间内特定条件下的每天数据条数）
        String dailyCountSql = buildDailyCountSql(works);
        Map<Date, Integer> dailyCountMap = new HashMap<Date, Integer>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        JDBCConnFactory factory = new JDBCConnFactory();
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = factory.getConnection("org.postgresql.Driver", "jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres");
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(dailyCountSql);
            while(rs.next()) {
                if(rs.getInt(2)>0){
                    dailyCountMap.put(sdf.parse(rs.getString(1)), rs.getInt(2));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            //关闭资源
        }

        //计算日期区间内符合条件的数据总条数
        long totalNum=dailyCountMap.values().stream()
                .mapToInt(Integer::intValue)
                .sum();
        long start=(works.getCurrent()-1)*works.getSize();
        long end=0;
        if(totalNum>works.getCurrent()*works.getSize()){
            end=works.getCurrent()*works.getSize();
        }else{
            end=totalNum;
        }
        long globalOffset = 0;//从第globalOffset条开始查询
        //根据分页信息，计算查询的当前页在哪个日期区间内
        List<String> currentPageSelectedDates = findDatesInterval(dailyCountMap,start,end,globalOffset);
        long globalLimit= end-start+1;
        //获取对应日期的查询sql
        String currentPageSql = buildCurrentPageSql(works,currentPageSelectedDates,globalOffset,globalLimit);
        ResultSet rs1 = null;
        List<Works> workList =new ArrayList<>();
        try {
            rs1 = stmt.executeQuery(currentPageSql);
            while(rs1.next()) {
                Works tempWork = new Works();
                tempWork.setTitle(rs1.getString(1));
                tempWork.setContent(rs1.getString(2));
                tempWork.setAuthorId(rs1.getString(3));
                tempWork.setCategory(rs1.getString(4));
                tempWork.setCreateTime(rs1.getString(5));
                workList.add(tempWork);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //关闭jdbc资源
        }
        IPage currentPage = new Page(works.getCurrent(), works.getSize());
        currentPage.setTotal(totalNum);
        currentPage.setRecords(workList);
        return ResultMap.success(currentPage);
    }
    //获取对应日期的查询sql
    private String buildCurrentPageSql(Works works,List<String> selectedDates,long globalOffset,long globalLimit ){
        StringBuilder whereClause = new StringBuilder("1=1");
        if(ObjectUtils.isNotEmpty(works.getAuthorId())){
            whereClause.append(" and author_id= '"+works.getAuthorId()+"'");
        }
        List<String> dailySqlList = new ArrayList<>();
        for (String date : selectedDates) {
            String dailyCountSql = buildDaySql(date,whereClause.toString(),globalOffset,globalLimit);
            dailySqlList.add(dailyCountSql);
        }
        return String.join(" UNION ALL ",dailySqlList);
    }
    private String buildDaySql(String date,String whereClause,long globalOffset,long globalLimit ){
      return  String.format("select title,content,author_id,category,create_time from works_%s where %s  LIMIT  %s OFFSET %s ",date,whereClause,globalLimit,globalOffset);
    }
    //查找数据区间在哪些日期中存在
    private List<String> findDatesInterval(Map<Date, Integer> dailyCountMap, long start, long end, long globalOffset) {
        List<String> result = new ArrayList<>();
        long currentStart=1;//索引从1开始
        List<Date> sortedDates= dailyCountMap.keySet().stream().sorted().collect(Collectors.toList());
        boolean includeStart = true;//用于设置第一次有交集时算 globalOffset
        for (Date date : sortedDates) {
            long count = dailyCountMap.get(date);
            long currrentEnd = currentStart+count-1;
            //检查区间是否有重叠
            if(hasOverLap(currentStart,currrentEnd,start,end)){
                if (includeStart){
                    globalOffset=start-currentStart;
                    includeStart = false;
                }
                result.add(new SimpleDateFormat("yyyyMMdd").format(date));
            }
            currentStart=currrentEnd+1;
        }
        return result;
    }
    private boolean hasOverLap(long start, long end, long start1, long end1) {
        return Math.max(start,start1)<=Math.min(end,end1);
    }
    //根据日期区间和特定条件生成要查询的sql
    private String buildDailyCountSql(Works works){
        StringBuilder whereClause = new StringBuilder("1=1");
        if(ObjectUtils.isNotEmpty(works.getAuthorId())){
            whereClause.append(" and author_id= '"+works.getAuthorId()+"'");
        }
        LocalDate startDate = LocalDate.parse(works.getStartDate());
        LocalDate endDate = LocalDate.parse(works.getEndDate());
        List<String> dailyCountSqlList = new ArrayList<>();
        LocalDate currentDate = startDate;
        while(!currentDate.isAfter(endDate)){
            String dailyCountSql = buildSingleDaySql(currentDate,whereClause.toString());
            dailyCountSqlList.add(dailyCountSql);
            currentDate = currentDate.plusDays(1);
        }
        return String.join(" UNION ALL ",dailyCountSqlList);
    }

    private String buildSingleDaySql(LocalDate currentDate,String whereClause){
        String sqlDate = currentDate.format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        return String.format("select %s selectedDate,count(*) selectedNum from works_%s where %s",sqlDate,sqlDate,whereClause);
    }
}
